package org.myframework.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.joda.time.DateTime;
import org.myframework.jdbc.cache.Cache;
import org.myframework.jdbc.cache.impl.EhcacheCache;
import org.myframework.jdbc.dialect.Dialect;
import org.myframework.jdbc.dialect.impl.OracleDialect;
import org.myframework.jdbc.generator.IdUtil;
import org.myframework.jdbc.metadata.ColumnInfo;
import org.myframework.jdbc.sqlconfig.SqlMapper;
import org.myframework.jdbc.sqltemplate.SqlTemplate;
import org.myframework.jdbc.sqltemplate.impl.VelocitySqlTemplate;
import org.myframework.util.ResultMap;
import org.myframework.util.StringUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;

/**
 * @ClassName: MyJdbcTemplate
 * @Description: 综合JdbcTemplate查询和myBatis SQL可配置的特性来实现可配置可热部署的JDBC查询
 * @author tony
 * @date 2014年12月12日 上午10:15:33
 * @deprecated  use NativeQuery instead of MyNamedParameterJdbcTemplate
 */
public class MyJdbcTemplate extends JdbcTemplate {
	private static Log log = LogFactory.getLog(MyJdbcTemplate.class);

	private static int DEFAULT_PAGE_SIZE = 50;

	protected Cache dataCache = new EhcacheCache(getClass().getName());

	private SqlTemplate sqlTemplate = new VelocitySqlTemplate();

	private Dialect dialect = new OracleDialect();
	private IdUtil idUtil = new IdUtil();
	/**
	 *
	 * @param mapperId
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> selectAllList(String sqlKey,
			Map<String, Object> map) {
		String cacheKey = getCacheKey(sqlKey, map);
		SqlMapper sqlMapper = sqlTemplate.getSqlMapper(sqlKey);
		String sql = sqlTemplate.getSql(sqlKey, map);
		if (sqlMapper == null) {
			throw new RuntimeException("load sqlconfig error :" + sqlKey);
		}
		if (sqlMapper.isFlushCache()) {
			dataCache.removeObject(cacheKey);
		}
		if (sqlMapper.isUseCache()) {
			if (dataCache.getObject(cacheKey) != null) {
				log.debug("load from cacheKey : " + cacheKey);
				return (List<Map<String, Object>>) dataCache
						.getObject(cacheKey);
			} else {
				log.debug("reload   cacheKey : " + cacheKey);
				List<Map<String, Object>> rs = this.queryForList(sql, map);
				dataCache.putObject(cacheKey, rs);
				return rs;
			}
		} else {
			List<Map<String, Object>> rs = this.queryForList(sql, map);
			return rs;
		}
	}

	/**
	 * @param sqlKey
	 * @param map
	 * @param offset
	 * @param limit
	 * @return offset+1~offset+limit
	 */
	public List<Map<String, Object>> selectPageList(String sqlKey,
			Map<String, Object> map, int offset, int limit) {
		log.debug("offset " + offset + " limit " + limit);
		long startTime = System.currentTimeMillis();
		String sql = sqlTemplate.getSql(sqlKey, map);
		if (dialect != null) {
			sql = dialect.getPageSql(sql, offset, limit, map);
			long endTime = System.currentTimeMillis();
			log.debug("getSql costTime ms :" + (endTime - startTime));
			log.debug(sql);
			log.debug(map);
			return this.queryForList(sql, map);
		} else {
			return selectLogicPageList(sqlKey, map, offset, limit);
		}

	}

	/**
	 * @param sqlKey
	 * @param map
	 * @param offset
	 * @param limit
	 * @return offset+1~offset+limit
	 * @throws SQLException
	 */
	public List<Map<String, Object>> selectLogicPageList(String sqlKey,
			Map<String, Object> map, int offset, int limit) {
		log.debug("offset " + offset + " limit " + limit);
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Map<String, Object>> rsList = new ArrayList<Map<String, Object>>(
				limit);
		String sql = sqlTemplate.getSql(sqlKey, map);
		long endTime = System.currentTimeMillis();
		log.debug("getSql costTime ms :" + (endTime - startTime));
		List<Object> params = new ArrayList<Object>();
		sql = this.sqlConvert(sql, map, params);
		log.debug(sql);
		log.debug(map);
		DataSource dataSource = this.getDataSource();
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setQueryTimeout(this.getQueryTimeout());
			for (int i = 0; i < params.size(); i++) {
				if (params.get(i) instanceof Integer)
					stmt.setInt(i + 1, ((Integer) params.get(i)).intValue());
				else if (params.get(i) instanceof Long)
					stmt.setLong(i + 1, ((Long) params.get(i)).longValue());
				else if (params.get(i) instanceof String)
					stmt.setString(i + 1, params.get(i).toString());
				else if (params.get(i) instanceof java.sql.Date)
					stmt.setDate(i + 1, (java.sql.Date) params.get(i));
				else if (params.get(i) instanceof java.sql.Timestamp)
					stmt.setTimestamp(i + 1, (java.sql.Timestamp) params.get(i));
				else if (params.get(i) instanceof java.util.Date)
					stmt.setDate(i + 1, new java.sql.Date(
							((java.util.Date) params.get(i)).getTime()));
				else if (params.get(i) instanceof Double)
					stmt.setDouble(i + 1,
							((Double) params.get(i)).doubleValue());
				else if (params.get(i) instanceof Float)
					stmt.setFloat(i + 1, ((Float) params.get(i)).floatValue());
				else
					stmt.setObject(i + 1, params.get(i));
			}
			rs = stmt.executeQuery();
			// scrollable result ≧ResultSeT
			List<ColumnInfo> columnInfos = getResultSetMetaData(rs);
			if (isScrollable(rs)) {
				// we can go straight to the first required row
				rs.absolute(offset);
				int rows = 0;
				while (rs.next()) {
					rows++;
					if (rows > limit) {
						break;
					} else {
						Map<String, Object> rsMap = new HashMap<String, Object>();
						for (ColumnInfo column : columnInfos)
							rsMap.put(column.getColumnName(), rs.getObject(column.getColumnName()));
						rsList.add(rsMap);
					}
				}
			} else {
				int i = 0;
				while (rs.next()) {
					i++;
					log.debug(i);
					if (i > offset && i <= offset + limit) {
						Map<String, Object> rsMap = new HashMap<String, Object>();
						for (ColumnInfo column : columnInfos)
							rsMap.put(column.getColumnName(), rs.getObject(column.getColumnName()));
						rsList.add(rsMap);
					}
					if (i > offset + limit)
						break;
				}
			}
		} catch (SQLException ex) {
			throw new RuntimeException(ex.getMessage());
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(conn, dataSource);
		}
		return rsList;
	}

	/**
	 * 返回SQL查询的列名信息
	 *
	 * @param sqlKey
	 * @param map
	 * @return
	 */
	public List<ColumnInfo> getSQLMetaData(String sqlKey, Map<String, Object> map) {
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = sqlTemplate.getSql(sqlKey, map);
		long endTime = System.currentTimeMillis();
		log.debug("getSql costTime ms :" + (endTime - startTime));
		List<Object> params = new ArrayList<Object>();
		sql = this.sqlConvert(sql, map, params);
		log.debug(sql);
		log.debug(map);
		List<ColumnInfo> colunmInfos = new ArrayList<ColumnInfo>();
		DataSource dataSource = this.getDataSource();
		try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setQueryTimeout(this.getQueryTimeout());
			for (int i = 0; i < params.size(); i++) {
				if (params.get(i) instanceof Integer)
					stmt.setInt(i + 1, ((Integer) params.get(i)).intValue());
				else if (params.get(i) instanceof Long)
					stmt.setLong(i + 1, ((Long) params.get(i)).longValue());
				else if (params.get(i) instanceof String)
					stmt.setString(i + 1, params.get(i).toString());
				else if (params.get(i) instanceof java.sql.Date)
					stmt.setDate(i + 1, (java.sql.Date) params.get(i));
				else if (params.get(i) instanceof java.sql.Timestamp)
					stmt.setTimestamp(i + 1, (java.sql.Timestamp) params.get(i));
				else if (params.get(i) instanceof java.util.Date)
					stmt.setDate(i + 1, new java.sql.Date(
							((java.util.Date) params.get(i)).getTime()));
				else if (params.get(i) instanceof Double)
					stmt.setDouble(i + 1,
							((Double) params.get(i)).doubleValue());
				else if (params.get(i) instanceof Float)
					stmt.setFloat(i + 1, ((Float) params.get(i)).floatValue());
				else
					stmt.setObject(i + 1, params.get(i));
			}
			rs = stmt.executeQuery();
			colunmInfos = getResultSetMetaData(rs);
		} catch (SQLException ex) {
			throw new RuntimeException(ex.getMessage());
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(conn, dataSource);
		}
		return colunmInfos;
	}

	/**
	 * 获取表字段相关信息(是否主键,字段名,字段类型,字段长度)
	 * @param strTableName
	 * @return
	 */
	public List<ColumnInfo> getTableColumnInfos(String strTableName) {
		DataSource dataSource = this.getDataSource();
		ResultSet rs = null;
		ResultSet rs2 = null;
		Connection conn = null;
		DatabaseMetaData dmd = null;
		List<ColumnInfo> columnInfos = new ArrayList<ColumnInfo>();
		try {
			conn = dataSource.getConnection();
			dmd = conn.getMetaData();
			rs = dmd.getColumns(null, "%", strTableName, "%");
			rs2 = dmd.getPrimaryKeys(null, "%", strTableName);
			final List<String> pkCols = getPkColumnInfos(rs2);
			while (rs.next()) {
				final String strFieldName = rs
						.getString(ColumnInfo.COLUMN_NAME);
				final String strFieldType = rs.getString(ColumnInfo.TYPE_NAME);
				final int nullable = rs.getInt(ColumnInfo.NULLABLE);
				final String fieldLabel = rs.getString(ColumnInfo.REMARKS);
				final int precision = rs.getInt(ColumnInfo.DECIMAL_DIGITS);
				final int colSize = rs.getInt(ColumnInfo.COLUMN_SIZE);
				final int charLen = rs.getInt(ColumnInfo.CHAR_OCTET_LENGTH);
				ColumnInfo columnInfo = new ColumnInfo() {
					@Override
					public boolean isPk() {
						return pkCols.contains(strFieldName);
					}

					@Override
					public String getColumnLabel() {
						return fieldLabel;
					}

					@Override
					public int getColumnSize() {
						return colSize;
					}

					@Override
					public String getColumnName() {
						return strFieldName;
					}

					@Override
					public String getColTypeName() {
						return strFieldType;
					}

					@Override
					public int getColPrecision() {
						return precision;
					}

					@Override
					public int getColScale() {
						return 0;
					}

					@Override
					public int getLength() {
						return charLen ;
					}

					@Override
					public int isNullable() {
						return nullable;
					}

					@Override
					public String toString() {
						return   StringUtil.toBeanPatternStr(strFieldName)
								+this.isPk() + ">>>"+ getColPrecision() + ">>>" +getColumnSize()   + ">>>"
								+ ">>>" + strFieldType + ">>>" + nullable+ ">>>"  + getColPrecision();
					}
				};
				columnInfos.add(columnInfo);
			}
		} catch (SQLException ex) {
			throw new RuntimeException(ex.getMessage());
		} finally {
			JdbcUtils.closeResultSet(rs2);
			JdbcUtils.closeResultSet(rs);
			DataSourceUtils.releaseConnection(conn, dataSource);
		}
		return columnInfos;
	}

	private List<String> getPkColumnInfos(ResultSet rs) throws SQLException {
		List<String> pkCols = new ArrayList<String>();
		while (rs.next()) {
			String strFieldName = rs.getString(ColumnInfo.COLUMN_NAME);
			pkCols.add(strFieldName);
		}
		return pkCols;
	}

	/**
	 * 获取查询结果集的列名信息
	 *
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 */
	private List<ColumnInfo> getResultSetMetaData(ResultSet resultSet) throws SQLException {
		List<ColumnInfo> list = new ArrayList<ColumnInfo>();
		ResultSetMetaData metadata = resultSet.getMetaData();
		int n = metadata.getColumnCount();
		for (int i = 1; i <= n; i++) {
			 final String colName = metadata.getColumnName(i);
			 final String typeName = metadata.getColumnTypeName(i);
			 final String colLabel = metadata.getColumnLabel( i);
			list.add(new ColumnInfo() {

				@Override
				public boolean isPk() {
					return false;
				}

				@Override
				public int isNullable() {
					return 0;
				}

				@Override
				public int getLength() {
					return 0;
				}

				@Override
				public int getColumnSize() {
					return 0;
				}

				@Override
				public String getColumnName() {
					return colName;
				}

				@Override
				public String getColumnLabel() {
					return colLabel;
				}

				@Override
				public String getColTypeName() {
					// TODO Auto-generated method stub
					return typeName;
				}

				@Override
				public int getColScale() {
					// TODO Auto-generated method stub
					return 0;
				}

				@Override
				public int getColPrecision() {
					// TODO Auto-generated method stub
					return 0;
				}
			});
		}
		return list;
	}

	/**
	 * scrollable result
	 *
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 */
	private boolean isScrollable(ResultSet resultSet) throws SQLException {
		int type = resultSet.getType();
		if (type == ResultSet.TYPE_SCROLL_INSENSITIVE
				|| type == ResultSet.TYPE_SCROLL_SENSITIVE)
			return true;
		else
			return false;
	}

	/**
	 *
	 * @param sqlKey
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> selectPageList(String sqlKey,
			Map<String, Object> map) {
		ResultMap<String, Object> rm = new ResultMap<String, Object>(map);
		int offset = rm.getInt(Dialect.OFFSET);
		int limit = rm.hasKey(Dialect.LIMIT) ? rm.getInt(Dialect.LIMIT)
				: DEFAULT_PAGE_SIZE;
		return this.selectPageList(sqlKey, map, offset, limit);
	}

	/**
	 *
	 * @param mapperId
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public Long selectCount(String sqlKey, Map<String, Object> map) {
		String sql = dialect.getCountString(sqlTemplate.getSql(sqlKey, map));
		List<Object> lstParam = new ArrayList<Object>();
		String querySql = this.sqlConvert(sql, map, lstParam);
		return this.queryForObject(querySql, lstParam.toArray(), Long.class);
	}

	/**
	 * 根据表字段数据类型的定义对map中的value值进行转化
	 * @param mapKey
	 * @param columnInfo
	 * @param map
	 */
	private void convertMapValue(String mapKey ,ColumnInfo columnInfo , Map<String, Object> map){
		String value = StringUtil.asString(map.get(mapKey));
		String colType = columnInfo.getColTypeName();
		if(dialect.isNumber(colType)&&!StringUtil.isEmpty(value) ){
			if(columnInfo.getColPrecision()>0)
				map.put(mapKey, Double.valueOf(value));
			else
				map.put(mapKey, Long.valueOf(value));
		}
		if(dialect.isDate(colType)&&!StringUtil.isEmpty(value) ){
			map.put(mapKey, DateTime.parse(value).toDate() );
		}
		if(columnInfo.isPk()){
			// 当新增操作时才传入idType参数，其他操作不能传入idType
			String idGeneratorType = StringUtil.asString(map.get("idType"));
			String sequence = StringUtil.asString(map.get("sequence"));
			if(!StringUtil.isEmpty(idGeneratorType)){
				idUtil.setJdbc(new NamedParameterJdbcTemplate(this));
				map.put(mapKey, idUtil.generateValue(idGeneratorType, sequence));
			}
		}
	}

	/**
	 * 将bean的名字转化对应表名 ，然后查询表字段 再结合传入的参数值，生成SQL语句
	 * @param beanName
	 * @param prefix
	 * @param map
	 * @return
	 */
	private String getDmlSql(String beanName,String prefix, Map<String, Object> map,String sqlType){
		String tableName = StringUtil.toDBPatternStr(beanName,prefix);
		List<ColumnInfo>  columnInfos = this.getTableColumnInfos(tableName);
		List<String> colsList= new ArrayList<String>();
		List<String> allCols= new ArrayList<String>();
		List<String> insertParamList= new ArrayList<String>();
		List<String> updateParamList= new ArrayList<String>();
		List<String> wherePkParamList= new ArrayList<String>();
		for (ColumnInfo columnInfo : columnInfos) {
			String colName = columnInfo.getColumnName();
			String mapKey= StringUtil.toBeanPatternStr(colName);
			allCols.add(colName);
			if(map.containsKey(mapKey)){
				colsList.add(colName);
				insertParamList.add(":"+mapKey);
				updateParamList.add(colName +"= :"+mapKey);
				convertMapValue(mapKey,columnInfo,map);
				if(columnInfo.isPk()){
					if(map.containsKey(mapKey)){
						wherePkParamList.add(colName +"= :"+mapKey);
					}
				}
			}
		}
		String createSql =  "INSERT INTO " + tableName + "(  "+ StringUtil.join(colsList ) +" ) VALUES ("  +  StringUtil.join(insertParamList) + " )";
		String updateSql =  "UPDATE " + tableName + " SET " +   StringUtil.join(updateParamList ) +"   WHERE   "+ StringUtil.join(wherePkParamList," AND ");
		String deleteSql =  "DELETE FROM  " + tableName + "    WHERE   "+ StringUtil.join(wherePkParamList," AND ");
		String readSql =    "SELECT "+ StringUtil.join(allCols ) +"  FROM  " + tableName + "    WHERE   "+ StringUtil.join(wherePkParamList," AND ");
		if("C".equals(sqlType)){
			return createSql;
		}else if("U".equals(sqlType)){
			return updateSql;
		}else if("D".equals(sqlType)){
			return deleteSql;
		}else  {
			return readSql;
		}
	}

	private String getCreateSql(String beanName,String prefix, Map<String, Object> map){
		return getDmlSql(beanName, prefix ,map ,"C");
	}

	private String getUpdateSql(String beanName,String prefix, Map<String, Object> map){
		return getDmlSql(beanName, prefix ,map ,"U");
	}

	private String getDeleteSql(String beanName,String prefix, Map<String, Object> map){
		return getDmlSql(beanName, prefix ,map ,"D");
	}


	private String getReadSql(String beanName,String prefix, Map<String, Object> map){
		return getDmlSql(beanName, prefix ,map ,"R");
	}

	/**
	 * @param beanName
	 * @param prefix
	 * @param param
	 */
	public void save (String beanName,String prefix, Map<String, Object> param){
		//TODO 添加新增方法
		String sql =getCreateSql(beanName,prefix,param) ;
		List<Object> paramList = new ArrayList<Object>();
		String updateSql = this.sqlConvert(sql, param, paramList);
		log.debug("-------------sql after convert--------------------");
		log.debug("\n\n" + this.getSql(updateSql, paramList) + "\n");
		log.debug("-------------sql after convert--------------------");
		this.update(updateSql, paramList.toArray());
	}

	public void save (String beanName, Map<String, Object> param){
		save(beanName,"",param);
	}

	/**
	 * @param beanName
	 * @param prefix
	 * @param param
	 */
	public void updateTable (String beanName,String prefix, Map<String, Object> param){
		//TODO 添加修改方法
		String sql =getUpdateSql(beanName ,prefix, param) ;
		List<Object> paramList = new ArrayList<Object>();
		String updateSql = this.sqlConvert(sql, param, paramList);
		log.debug("-------------sql after convert--------------------");
		log.debug("\n\n" + this.getSql(updateSql, paramList) + "\n");
		log.debug("-------------sql after convert--------------------");
		this.update(updateSql, paramList.toArray());
	}

	public void updateTable (String beanName,  Map<String, Object> param){
		updateTable(beanName,"",param);
	}
	/**
	 * @param beanName
	 * @param prefix
	 * @param param
	 */
	public void delete (String beanName,String prefix, Map<String, Object> param){
		String sql = getDeleteSql(beanName,prefix, param) ;
		List<Object> paramList = new ArrayList<Object>();
		String updateSql = this.sqlConvert(sql, param, paramList);
		log.debug("-------------sql after convert--------------------");
		log.debug("\n\n" + this.getSql(updateSql, paramList) + "\n");
		log.debug("-------------sql after convert--------------------");
		this.update(updateSql, paramList.toArray());
	}

	public void delete (String beanName,  Map<String, Object> param){
		delete(beanName,"",param);
	}

	/**
	 * @param beanName
	 * @param prefix
	 * @param param
	 */
	public  Map<String, Object> read (String beanName,String prefix, Map<String, Object> param){
		String sql = getReadSql(beanName,prefix, param) ;
		return this.queryForMap(sql, param) ;
	}

	public  Map<String, Object> read (String beanName,  Map<String, Object> param){
		return read(beanName,"",param);
	}

	/**
	 * sql example: update table set colname = '' where user_id = :userId
	 *
	 * @param sqlKey
	 * @param map
	 */
	public int updateBySqlKey(String sqlKey, Map<String, Object> map) {
		String sql = sqlTemplate.getSql(sqlKey, map);
		log.debug("-------------sql before convert--------------------");
		log.debug("\n\n" + sql + "\n");
		log.debug(map);
		log.debug("-------------sql before convert--------------------");
		List<Object> paramList = new ArrayList<Object>();
		String updateSql = this.sqlConvert(sql, map, paramList);
		log.debug("-------------sql after convert--------------------");
		log.debug("\n\n" + this.getSql(updateSql, paramList) + "\n");
		log.debug("-------------sql after convert--------------------");
		return this.update(updateSql, paramList.toArray());
	}

	/**
	 * @param sql
	 *            example:select * from table where user_id = :userId
	 * @param map
	 */
	public List<Map<String, Object>> queryForList(String sql,
			Map<String, Object> map) {
		List<Object> lstParam = new ArrayList<Object>();
		log.debug("-------------sql before convert--------------------");
		log.debug("\n\n" + sql + "\n");
		log.debug(map);
		log.debug("-------------sql before convert--------------------");
		String querySql = this.sqlConvert(sql, map, lstParam);
		log.debug("-------------sql after convert--------------------");
		log.debug("\n\n" + this.getSql(querySql, lstParam) + "\n");
		log.debug("-------------sql after convert--------------------");
		return this.queryForList(querySql, lstParam.toArray());
	}

	/**
	 * select * from table where user_id = :userId
	 *
	 * @param sql
	 * @param map
	 */
	public Map<String, Object> queryForMap(String sql, Map<String, Object> map) {
		List<Object> paramList = new ArrayList<Object>();
		String querySql = this.sqlConvert(sql, map, paramList);
		log.debug(this.getSql(querySql, paramList));
		return this.queryForMap(querySql, paramList.toArray());
	}

	/**
	 *
	 * EXAMPLE: sql:select COUNT(1) cnt from table where user_id = :userId map:
	 * { userId : "1"} requiredType : Long.class return Long value of cnt
	 *
	 * @param sql
	 * @param map
	 * @param requiredType
	 * @return
	 */
	public <T> T selectForObject(String sql, Map<String, Object> map,
			Class<T> requiredType) {
		List<Object> paramList = new ArrayList<Object>();
		String querySql = this.sqlConvert(sql, map, paramList);
		return queryForObject(querySql, paramList.toArray(),
				getSingleColumnRowMapper(requiredType));
	}

	/**
	 * @param sql
	 *            example: update table set colname = '' where user_id = :userId
	 * @param map
	 */
	public int update(String sql, Map<String, Object> map) {
		List<Object> paramList = new ArrayList<Object>();
		String updateSql = this.sqlConvert(sql, map, paramList);
		return this.update(updateSql, paramList.toArray());
	}

	/**
	 * sql ( select * from table where user_id = :userId ) Convert to standard
	 * jdbc sql ( select * from table where user_id = ?) ;
	 *
	 * @param sql
	 * @param map
	 * @param list
	 * @return
	 */
	@SuppressWarnings("all")
	private String sqlConvert(String sql, Map qryMap, List<Object> list) {
		// replace :param to bindValue
		if (qryMap != null && !qryMap.isEmpty()) {
			List<String> params = getParameterList(sql);
			for (String param : params) {
				list.add(qryMap.get(param));
				sql = StringUtils.replaceOnce(sql, ":" + param, "?");
			}
		}
		// replace #param to value
		if (qryMap != null && !qryMap.isEmpty()) {
			List<String> params = getReplaceParameterList(sql);
			for (String param : params) {
				sql = StringUtils.replaceOnce(sql, "#" + param,
						StringUtil.asString(qryMap.get(param)));
			}
		}
		return sql;
	}

	/**
	 * sql ( select * from table where user_id = :userId ) :userId will be added
	 * into list as param
	 *
	 * @param sql
	 * @return
	 */
	private List<String> getParameterList(String sql) {
		String regex = "\\:(\\w+)";
		Pattern pattern = Pattern.compile(regex);
		Matcher matcher = pattern.matcher(sql);
		List<String> params = new ArrayList<String>();
		while (matcher.find())
			params.add(matcher.group(1));
		return params;
	}

	private List<String> getReplaceParameterList(String sql) {
		String regex = "\\#(\\w+)";
		Pattern pattern = Pattern.compile(regex);
		Matcher matcher = pattern.matcher(sql);
		List<String> params = new ArrayList<String>();
		while (matcher.find())
			params.add(matcher.group(1));
		return params;
	}

	private String getSql(String sql, List<Object> lstParam) {
		for (int i = 0; i < lstParam.size(); i++) {
			Object value = lstParam.get(i);
			if (value instanceof Integer || value instanceof Double
					|| value instanceof Float || value instanceof Long) {
				sql = StringUtils.replaceOnce(sql, "?", value.toString());
			} else if (value instanceof String) {
				sql = StringUtils.replaceOnce(sql, "?", "'" + value.toString()
						+ "'");
			} else {
				sql = StringUtils.replaceOnce(sql, "?",
						"'" + String.valueOf(value) + "'");
			}
		}
		return sql;
	}

	public String getSql(String sqlKey, Map<String, Object> map) {
		String sql = sqlTemplate.getSql(sqlKey, map);
		return "[" + sqlKey + "]" + getQrySql(sql, map);
	}

	public String getQrySql(String sql, Map<String, Object> map) {
		List<Object> lstParam = new ArrayList<Object>();
		String querySql = this.sqlConvert(sql, map, lstParam);
		return this.getSql(querySql, lstParam);
	}

	private String getCacheKey(String sqlKey, Map<String, Object> qryMap) {
		StringBuffer cacheKey = new StringBuffer(sqlKey);
		if (qryMap != null && !qryMap.isEmpty())
			cacheKey.append(cacheKey.toString()).append("_")
					.append(qryMap.toString());
		return cacheKey.toString();
	}

	public void setSqlTemplate(SqlTemplate sqlTemplate) {
		this.sqlTemplate = sqlTemplate;
	}

	public void setDataSource(DataSource dataSource) {
		super.setDataSource(dataSource);
	}

	public void setDialect(Dialect dialect) {
		this.dialect = dialect;
	}

	public void setDataCache(Cache dataCache) {
		this.dataCache = dataCache;
	}

}
